﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace SWEN_HMS.Classes
{
    public class StaffHousekeepingDBManager
    {
        public static List<int> getRoomNumList()
        {
            List<int> resultSet = new List<int>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    resultSet.Add((int)dr["roomNo"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static int addRequest(Request request)
        {
            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Request (requestRemarks, requestDateTime, roomNo) VALUES (@requestRemarks, @requestDateTime, @roomNo)";
                comm.Parameters.AddWithValue("@requestRemarks", request.requestRemarks);
                comm.Parameters.AddWithValue("@requestDateTime", request.requestDateTime);
                comm.Parameters.AddWithValue("@roomNo", request.roomNo);
                result = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static int getLatestRequestID()
        {
            int latestRequestID = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT requestID FROM Request";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    latestRequestID = (int)dr["requestID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return latestRequestID;
        }

        public static DataTable getRequests()
        {
            DataTable result = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Request";
                SqlDataReader dr = comm.ExecuteReader();
                result = new DataTable();
                result.Load(dr);
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool deleteRequest(int id)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Request WHERE requestID=@requestID";
                comm.Parameters.AddWithValue("@requestID", id);
                int ifDeleted = 0;
                ifDeleted = comm.ExecuteNonQuery();
                if (ifDeleted == 1)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static Request getRequestByID(int id)
        {
            Request result = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Request WHERE requestID=@requestID";
                comm.Parameters.AddWithValue("@requestID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = new Request();
                    result.requestID = (int)dr["requestID"];
                    result.requestRemarks = (string)dr["requestRemarks"];
                    result.requestDateTime = (DateTime)dr["requestDateTime"];
                    result.roomNo = (int)dr["roomNo"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool updateRequest(Request request)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Request SET requestRemarks=@requestRemarks, requestDateTime=@requestDateTime, roomNo=@roomNo WHERE requestID=@requestID";
                comm.Parameters.AddWithValue("@requestID", request.requestID);
                comm.Parameters.AddWithValue("@requestRemarks", request.requestRemarks);
                comm.Parameters.AddWithValue("@requestDateTime", request.requestDateTime);
                comm.Parameters.AddWithValue("@roomNo", request.roomNo);
                int ifUpdated = 0;
                ifUpdated = comm.ExecuteNonQuery();
                if (ifUpdated == 1)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static DataTable getAllStaffs()
        {
            DataTable resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff";
                SqlDataReader dr = comm.ExecuteReader();
                resultSet = new DataTable();
                resultSet.Load(dr);
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static bool deleteStaff(int id)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm1 = new SqlCommand();
                comm1.Connection = conn;
                comm1.CommandText = "DELETE FROM Housekeeping WHERE staffID=@staffID";
                comm1.Parameters.AddWithValue("@staffID", id);
                int ifDeleted1 = 0;
                ifDeleted1 = comm1.ExecuteNonQuery();
                if (ifDeleted1 == 0)
                {
                    result = false;
                }
                else
                {
                    result = true;
                }

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", id);
                int ifDeleted = 0;
                ifDeleted = comm.ExecuteNonQuery();
                if (ifDeleted == 1)
                {
                    result = true;
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static List<int> getStaffIDHousekeepingList()
        {
            List<int> resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    if (((string)dr["position"] == "General Maintenance") || ((string)dr["position"] == "Room Maintenance")
                        || ((string)dr["position"] == "Estate Maintenance") || ((string)dr["position"] == "Security")) // if staff is under Room Maintenance
                    {
                        if (resultSet == null)
                        {
                            resultSet = new List<int>();
                        }
                        resultSet.Add((int)dr["staffID"]);
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static string getStaffNameByStaffIDHousekeeping(int staffID)
        {
            string result = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT staffName FROM Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = (string)dr["staffName"];
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static string getStaffPositionHousekeeping(int staffID)
        {
            string result = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = (string)dr["position"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool ifStaffIsRoom(int staffID)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    if ((string)dr["position"] == "Room Maintenance")
                    {
                        result = true;
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static int addSchedule(Housekeeping hk, string position)
        {
            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                if (position == "Room Maintenance")
                {
                    comm.CommandText = "INSERT INTO Housekeeping (staffID, roomNo, dutyDateTime, remarks) VALUES (@staffID, @roomNo, @dutyDateTime, @remarks)";
                }
                else
                {
                    comm.CommandText = "INSERT INTO Housekeeping (staffID, dutyDateTime, remarks) VALUES (@staffID, @dutyDateTime, @remarks)";
                }
                comm.Parameters.AddWithValue("@staffID", hk.staffID);
                if (position == "Room Maintenance")
                {
                    comm.Parameters.AddWithValue("@roomNo", hk.roomNo);
                }
                comm.Parameters.AddWithValue("@dutyDateTime", hk.dutyDateTime);
                comm.Parameters.AddWithValue("@remarks", hk.remarks);
                result = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static List<Housekeeping> getScheduleByDay(string day)
        {
            List<Housekeeping> resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Housekeeping";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    DateTime dateTime = (DateTime)dr["dutyDateTime"];
                    if (Convert.ToString(dateTime.DayOfWeek) == day)
                    {
                        if (resultSet == null)
                        {
                            resultSet = new List<Housekeeping>();
                        }
                        Housekeeping h = new Housekeeping();
                        h.housekeepingID = (int)dr["housekeepingID"];
                        h.staffID = (int)dr["staffID"];
                        if (dr["roomNo"] != DBNull.Value)
                        {
                            h.roomNo = (int)dr["roomNo"];
                        }
                        else
                        {
                            h.roomNo = 0;
                        }
                        h.remarks = (string)dr["remarks"];
                        h.dutyDateTime = dateTime;
                        resultSet.Add(h);
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static List<Housekeeping> getScheduleByStaff(int stfID)
        {
            List<Housekeeping> resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Housekeeping WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", stfID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    if (resultSet == null)
                    {
                        resultSet = new List<Housekeeping>();
                    }
                    Housekeeping h = new Housekeeping();
                    h.housekeepingID = (int)dr["housekeepingID"];
                    h.staffID = (int)dr["staffID"];
                    if (dr["roomNo"] != DBNull.Value)
                    {
                        h.roomNo = (int)dr["roomNo"];
                    }
                    else
                    {
                        h.roomNo = 0;
                    }
                    h.remarks = (string)dr["remarks"];
                    h.dutyDateTime = (DateTime)dr["dutyDateTime"];
                    resultSet.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static List<Housekeeping> getScheduleByRoomNo(int roomNo)
        {
            List<Housekeeping> resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Housekeeping WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    if (resultSet == null)
                    {
                        resultSet = new List<Housekeeping>();
                    }
                    Housekeeping h = new Housekeeping();
                    h.housekeepingID = (int)dr["housekeepingID"];
                    h.staffID = (int)dr["staffID"];
                    if (dr["roomNo"] != DBNull.Value)
                    {
                        h.roomNo = (int)dr["roomNo"];
                    }
                    else
                    {
                        h.roomNo = 0;
                    }
                    h.remarks = (string)dr["remarks"];
                    h.dutyDateTime = (DateTime)dr["dutyDateTime"];
                    resultSet.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static List<int> getStaffIDsByHousekeepingRole(string role)
        {
            List<int> resultSet = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE position=@position";
                comm.Parameters.AddWithValue("@position", role);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    if (resultSet == null)
                    {
                        resultSet = new List<int>();
                    }
                    resultSet.Add((int)dr["staffID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return resultSet;
        }

        public static bool deleteSchedule(int id)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Housekeeping WHERE housekeepingID=@housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", id);
                int ifDeleted = 0;
                ifDeleted = comm.ExecuteNonQuery();
                if (ifDeleted == 1)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static Housekeeping getScheduleForUpdate(int hkID)
        {
            Housekeeping result = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Housekeeping WHERE housekeepingID=@housekeepingID";
                comm.Parameters.AddWithValue("@housekeepingID", hkID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = new Housekeeping();
                    result.housekeepingID = (int)dr["housekeepingID"];
                    result.staffID = (int)dr["staffID"];
                    if (dr["roomNo"] != DBNull.Value)
                    {
                        result.roomNo = (int)dr["roomNo"];
                    }
                    else
                    {
                        result.roomNo = 0;
                    }
                    result.dutyDateTime = (DateTime)dr["dutyDateTime"];
                    result.remarks = (string)dr["remarks"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool updateSchedule(Housekeeping hk, string position)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                if (position == "Room Maintenance")
                {
                    comm.CommandText = "UPDATE Housekeeping SET staffID=@staffID, roomNo=@roomNo, dutyDateTime=@dutyDateTime, remarks=@remarks WHERE housekeepingID=@housekeepingID";
                }
                else
                {
                    comm.CommandText = "UPDATE Housekeeping SET staffID=@staffID, dutyDateTime=@dutyDateTime, remarks=@remarks WHERE housekeepingID=@housekeepingID";
                }
                comm.Parameters.AddWithValue("@housekeepingID", hk.housekeepingID);
                comm.Parameters.AddWithValue("@staffID", hk.staffID);
                if (position == "Room Maintenance")
                {
                    comm.Parameters.AddWithValue("@roomNo", hk.roomNo);
                }
                comm.Parameters.AddWithValue("@dutyDateTime", hk.dutyDateTime);
                comm.Parameters.AddWithValue("@remarks", hk.remarks);
                int ifUpdated = comm.ExecuteNonQuery();
                if (ifUpdated == 1)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
    }
}